﻿using _066.App;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Xml.Linq;

namespace _066
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            this.getRusult();
        }
        private void getRusult()//显示dataGridView1数据
        {
            string constr = "server=.;database=Students;user=sa;pwd=35683568";
            SqlConnection conn = new SqlConnection(constr);
            conn.Open();
            //textBox1.Text.Trim()  textBox2.Text.Trim()
            string sql = "select Code as 用户id, Name as 用户名,Pwd as 密码,Sex as 性别,Age as 年龄,Phone as 手机号 from Temp_users";
            SqlDataAdapter adp1 = new SqlDataAdapter(sql, conn);
            DataSet ds = new DataSet();
            adp1.Fill(ds);
            //载入基本信息
            dataGridView1.DataSource = ds.Tables[0].DefaultView;
            conn.Close();
        }
        #region
        private void btnAdd_Click(object sender, EventArgs e)//增加
        {
            Form2 insert = new Form2();//新建一个窗体打开
            insert.Owner = this;
            insert.Show();
        }

        private void btnDelete_Click(object sender, EventArgs e)//删除
        {
            #region
            ////创建连接字符串
            //string constr = "server=.;database=MyDatabaseOne;user=sa;pwd=35683568";
            //SqlConnection connection = new SqlConnection(constr);
            //try
            //{
            //    connection.Open();
            //    SqlCommand cmd = new SqlCommand();
            //    cmd.Connection = connection;
            //    //创建命令项目
            //    //删除的数据
            //    string sqlstr = "delete from users where code=4";
            //    cmd.CommandText = sqlstr;
            //    //执行命令
            //    int i = cmd.ExecuteNonQuery();
            //    Text = i.ToString();
            //    if (i > 0)
            //    {
            //        MessageBox.Show("执行成功");
            //    }
            //    else
            //    {
            //        MessageBox.Show("执行失败");
            //    }
            //}
            //catch (Exception ex)
            //{

            //}
            #endregion
            //1.创建连接数据库字符串
            string currentIndex = dataGridView1.CurrentRow.Cells[0].Value.ToString();//获取dataGridView控件选中行
            string constr = "server=.;database=Students;user=sa;pwd=35683568";
            SqlConnection connection = new SqlConnection(constr);
            try
            {
                connection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = connection;
                cmd = connection.CreateCommand();
                //拼写语句
                cmd.CommandText = "delete from users where code='" + currentIndex + "'";
                int count = cmd.ExecuteNonQuery();
                if (count > 0)
                {
                    MessageBox.Show("删除成功");
                }
                else
                {
                    MessageBox.Show("删除失败");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("执行语句有误，请查验");
            }

        }

        private void bntSelect_Click(object sender, EventArgs e)//查询所有数据
        {
            //创建连接字符串
            SqlConnection conn = new SqlConnection("server=.;database=Students;user=sa;pwd=35683568");
            conn.Open();
            SqlCommand cmd = new SqlCommand("select * from users", conn);
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            sda.Fill(ds);
            DataTable dt = ds.Tables[0];
            dataGridView1.AutoGenerateColumns = false;
            dataGridView1.DataSource = ds.Tables[0];
            conn.Close();
        }
        #endregion
        private void btnRevise_Click(object sender, EventArgs e)//修改
        {
            string constr = "server=.;database=Students;user=sa;pwd=35683568";
            SqlConnection conn = new SqlConnection(constr);
            conn.Open();
            int id = 0;
            int.TryParse(textBox1.Text, out id);
            string sql = "update Temp_users set Name = '" + textBox2.Text + "',Pwd = '" + textBox3.Text +"', Sex='"+textBox4.Text+"',Age="+textBox5.Text+",Phone="+textBox6.Text+" where  Code = " + id;
            
            SqlCommand cmd = new SqlCommand(sql, conn);
            if (cmd.ExecuteNonQuery() > 0)
            {
                this.getRusult();
                MessageBox.Show("更改成功！");
            }
            conn.Close();

        }
        #region
        private void groupBox2_Enter(object sender, EventArgs e)
        {

        }

        private void button1_Click(object sender, EventArgs e)//按数据库查询
        {
            try
            {
                SqlConnection conn = new SqlConnection("server=.;database=Students;user=sa;pwd=35683568");
                conn.Open();
                SqlCommand cmd = new SqlCommand();//创建对象
                cmd.Connection = conn;
                cmd.CommandText = "select count(*) from " + textuser.Text.Trim();
                cmd.CommandType = CommandType.Text;
                int i = Convert.ToInt32(cmd.ExecuteScalar());
                lbdata.Text = "数据库表中共有" + i.ToString() + "条数据";
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }

        private void label1_Click(object sender, EventArgs e)
        {

        }

        private void button2_Click(object sender, EventArgs e)//按字段查询
        {
            //创建连接字符串
            SqlConnection conn = new SqlConnection("server=.;database=Students;user=sa;pwd=35683568");
            conn.Open();
            string sqlstring = "select * from users where name='" + this.textfiled.Text.Trim() + "'";
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand(sqlstring, conn);
            try
            {
                dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            this.dataGridView1.DataSource = dt;
        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
        }
        #endregion
        private void dataGridView1_RowHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)//点击DataGridView头部行
        {
            if (dataGridView1.SelectedCells.Count != 0)
            {
                textBox1.Text = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
                textBox2.Text = dataGridView1.SelectedRows[0].Cells[1].Value.ToString();
                textBox3.Text = dataGridView1.SelectedRows[0].Cells[2].Value.ToString();
                textBox4.Text = dataGridView1.SelectedRows[0].Cells[3].Value.ToString();
                textBox5.Text = dataGridView1.SelectedRows[0].Cells[4].Value.ToString();
                textBox6.Text = dataGridView1.SelectedRows[0].Cells[5].Value.ToString();

            }

        }
    }
}
